package rsj.service.report.dynamic;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import rsj.api.report.entity.ReportParam;

/**
 * 客户接口报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class CustomerProductReportDynamicStatement {



    //**********************************客户接口报表*************************************//


    /**
     * 客户接口报告列出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportListSql(ReportParam reportParam) {
        return this.customerProductReportExportSql(reportParam) + " OFFSET #{pageOffset} LIMIT #{pageSize}";
    }


    /**
     * 客户接口报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportExportSql(ReportParam reportParam) {
        String sql = "SELECT\n" +
                "stat_prod_uid AS prod_uid,max(stat_prod_name) AS prod_name,\n" +
                "CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id,\n" +
                "max(c.cust_sname) as cust_sname,\n" +
                "sum(stat_req_num) as stat_req_num,\n" +
                "sum(stat_findout_num) as stat_findout_num,\n" +
                "sum(stat_fail_num) as stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o on o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c on c.cust_id = o.org_cust_id\n" +
                "LEFT JOIN sys.sys_prod p on p.prod_uid = sta.stat_prod_uid\n" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.customerProductReportQueryConditions(reportParam);
        sql += " GROUP BY c.cust_id,stat_prod_uid";
        return sql;
    }


    /**
     * 客户接口报告统计sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportCountSql(ReportParam reportParam) {
        String sql = "SELECT COUNT(*) FROM (\n" +
                "     SELECT stat_prod_uid, c.cust_id\n" +
                " FROM sys.sys_stat sta\n" +
                " LEFT JOIN sys.sys_org o on o.org_id = sta.stat_org_id\n" +
                " LEFT JOIN sys.sys_cust c on c.cust_id = o.org_cust_id\n" +
                " LEFT JOIN sys.sys_prod p on p.prod_uid = sta.stat_prod_uid WHERE stat_date BETWEEN #{startDate} AND #{endDate}";
        sql += this.customerProductReportQueryConditions(reportParam) + " GROUP BY c.cust_id,stat_prod_uid ) a";
        return sql;
    }



    /**
     * 客户接口报表查询条件
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportQueryConditions(ReportParam reportParam) {
        String sql = "";
        if (StringUtils.isNotBlank(reportParam.getCustSname())) {
            sql += " AND cust_sname LIKE #{%custSname%}";
        }
        if (StringUtils.isNotBlank(reportParam.getCustId())) {
            sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) LIKE #{%custId%}";
        }
        if (StringUtils.isNotBlank(reportParam.getProdUid())) {
            sql += " AND prod_uid LIKE #{%prodUid%}";
        }
        return sql;
    }







    /**
     * 客户接口报告详情sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportDetailSql(ReportParam reportParam) {
        String sql = this.customerProductReportDetailExportSql(reportParam);
        return sql + " OFFSET #{pageOffset} LIMIT #{pageSize}";
    }


    /**
     * 客户接口报告详情导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportDetailExportSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        String sql = "SELECT\n" +
                "TO_CHAR(stat_date,'" + grouping + "') AS stat_date,\n" +
                " CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) AS cust_id ,\n" +
                " cust_sname,prod_uid,\n" +
                "MAX(prod_name) AS prod_name,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_org o ON o.org_id = sta.stat_org_id\n" +
                "LEFT JOIN sys.sys_cust c ON c.cust_id = o.org_cust_id\n" +
                "LEFT JOIN sys.sys_prod p on p.prod_uid = sta.stat_prod_uid WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND " +
                "CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) = #{custId} AND prod_uid = #{prodUid}";
        sql += " GROUP BY c.cust_id,prod_uid,TO_CHAR(stat_date,'" + grouping + "')";
        return sql;
    }


    /**
     * 客户接口报告详情统计sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String customerProductReportDetailCountSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        String sql = "SELECT COUNT(*) FROM (\n" +
                " SELECT TO_CHAR(stat_date, '" + grouping + "') AS stat_date,\n" +
                " CONCAT('RSJ', LPAD(c.cust_id::text, 4, '0')) AS cust_id, prod_uid\n" +
                " FROM sys.sys_stat sta\n" +
                " LEFT JOIN sys.sys_org o ON o.org_id = sta.stat_org_id\n" +
                " LEFT JOIN sys.sys_cust c ON c.cust_id = o.org_cust_id\n" +
                " LEFT JOIN sys.sys_prod p on p.prod_uid = sta.stat_prod_uid WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND " +
                " CONCAT('RSJ',LPAD(c.cust_id::text,4,'0')) = #{custId} AND prod_uid = #{prodUid}";
        sql += " GROUP BY c.cust_id,prod_uid, TO_CHAR(stat_date, '" + grouping + "')\n" +
                ") a";
        return sql;
    }


}
